package Test;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.UUID;

import PluSoft.Utils.StringUtil;

public class TestDB
{

	public static String driver = "com.mysql.jdbc.Driver";
	public static String url = "jdbc:mysql://localhost/plusoft_test?useUnicode=true&characterEncoding=GBK";
	public static String user = "root";
	public static String pwd = "";

	// //////////////////////////////////////////////////
	public ArrayList GetDepartments() throws Exception
	{
		final String sql = "select *" + " from t_department";
		final ArrayList data = DBSelect(sql);
		return data;
	}

	public HashMap GetDepartment(String id) throws Exception
	{
		final String sql = "select * from t_department where id = '" + id + "'";
		final ArrayList data = DBSelect(sql);
		return data.size() > 0 ? (HashMap) data.get(0) : null;
	}

	public ArrayList GetPositions() throws Exception
	{
		final String sql = "select * from t_position";
		final ArrayList data = DBSelect(sql);
		return data;
	}

	public ArrayList GetEducationals() throws Exception
	{
		final String sql = "select * from t_educational";
		final ArrayList data = DBSelect(sql);
		return data;
	}

	public ArrayList GetPositionsByDepartmenId(String departmentId) throws Exception
	{
		final String sql = "select * from t_position where dept_id = '" + departmentId + "'";
		final ArrayList dataAll = DBSelect(sql);
		return dataAll;

	}

	public HashMap GetDepartmentEmployees(String departmentId, int index, int size) throws Exception
	{
		final String sql = "select * from t_employee where dept_id = '" + departmentId + "'";
		final ArrayList dataAll = DBSelect(sql);

		final ArrayList data = new ArrayList();
		final int start = index * size, end = start + size;

		for (int i = 0, l = dataAll.size(); i < l; i++)
		{
			final HashMap record = (HashMap) dataAll.get(i);
			if (record == null)
				continue;
			if ((start <= i) && (i < end))
			{
				data.add(record);
			}
		}

		final HashMap result = new HashMap();
		result.put("data", data);
		result.put("total", dataAll.size());

		return result;
	}

	public HashMap SearchEmployees(String key, int index, int size, String sortField, String sortOrder) throws Exception
	{
		// System.Threading.Thread.Sleep(300);
		if (key == null)
			key = "";

		String sql = "select a.*, b.name dept_name, c.name position_name, d.name educational_name \n" + "from t_employee a \n" + "left join t_department b \n" + "on a.dept_id = b.id \n" + "left join t_position c \n" + "on a.position = c.id \n"
				+ "left join t_educational d \n" + "on a.educational = d.id \n" + "where a.name like '%" + key + "%' \n";

		if (StringUtil.isNullOrEmpty(sortField) == false)
		{
			if ("desc".equals(sortOrder) == false)
				sortOrder = "asc";
			sql += " order by " + sortField + " " + sortOrder;
		}
		else
		{
			sql += " order by createtime desc";
		}

		final ArrayList dataAll = DBSelect(sql);

		final ArrayList data = new ArrayList();
		final int start = index * size, end = start + size;

		for (int i = 0, l = dataAll.size(); i < l; i++)
		{
			final HashMap record = (HashMap) dataAll.get(i);
			if (record == null)
				continue;
			if ((start <= i) && (i < end))
			{
				data.add(record);
			}
		}

		final HashMap result = new HashMap();
		result.put("data", data);
		result.put("total", dataAll.size());

		// minAge, maxAge, avgAge
		final ArrayList ages = DBSelect("select min(age) as minAge, max(age) as maxAge, avg(age) as avgAge from t_employee");
		final HashMap ageInfo = (HashMap) ages.get(0);
		result.put("minAge", ageInfo.get("minAge"));
		result.put("maxAge", ageInfo.get("maxAge"));
		result.put("avgAge", ageInfo.get("avgAge"));

		return result;
	}

	public HashMap GetEmployee(String id) throws Exception
	{
		final String sql = "select * from t_employee where id = '" + id + "'";
		final ArrayList data = DBSelect(sql);
		return data.size() > 0 ? (HashMap) data.get(0) : null;
	}

	public String InsertEmployee(HashMap user) throws Exception
	{
		final String id = ((user.get("id") == null) || user.get("id").toString().equals("")) ? UUID.randomUUID().toString() : user.get("id").toString();
		user.put("id", id);

		if (user.get("name") == null)
			user.put("name", "");
		if (StringUtil.isNullOrEmpty(user.get("gender")))
			user.put("gender", 0);

		final Connection conn = getConn();

		final String sql = "insert into t_employee (id, loginname, name, age, married, gender, birthday, country, city, dept_id, position, createtime, salary, educational, school, email, remarks)"
				+ " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		final PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setString(1, ToString(user.get("id")));
		stmt.setString(2, ToString(user.get("loginname")));
		stmt.setString(3, ToString(user.get("name")));
		stmt.setInt(4, ToInt(user.get("age")));
		stmt.setInt(5, ToInt(user.get("married")));
		stmt.setInt(6, ToInt(user.get("gender")));
		stmt.setTimestamp(7, ToDate(user.get("birthday")));
		stmt.setString(8, ToString(user.get("country")));
		stmt.setString(9, ToString(user.get("city")));
		stmt.setString(10, ToString(user.get("dept_id")));
		stmt.setString(11, ToString(user.get("position")));
		stmt.setTimestamp(12, ToDate(user.get("createtime")));
		stmt.setString(13, ToString(user.get("salary")));
		stmt.setString(14, ToString(user.get("educational")));
		stmt.setString(15, ToString(user.get("school")));
		stmt.setString(16, ToString(user.get("email")));
		stmt.setString(17, ToString(user.get("remarks")));

		stmt.executeUpdate();

		stmt.close();
		conn.close();

		return id;
	}

	public void DeleteEmployee(String userId) throws Exception
	{

		final Connection conn = getConn();
		final Statement stmt = conn.createStatement();

		final String sql = "delete from t_employee where id = \"" + userId + "\"";
		stmt.executeUpdate(sql);

		stmt.close();
		conn.close();
	}

	public void UpdateEmployee(HashMap user) throws Exception
	{
		final HashMap db_user = GetEmployee(user.get("id").toString());

		final Iterator iter = user.entrySet().iterator();
		while (iter.hasNext())
		{
			final Map.Entry entry = (Map.Entry) iter.next();
			final Object key = entry.getKey();
			final Object val = entry.getValue();

			db_user.put(key, val);
		}

		DeleteEmployee(user.get("id").toString());
		InsertEmployee(db_user);
	}

	public void UpdateDepartment(HashMap d) throws Exception
	{
		final HashMap db_d = GetDepartment(d.get("id").toString());

		final Iterator iter = d.entrySet().iterator();
		while (iter.hasNext())
		{
			final Map.Entry entry = (Map.Entry) iter.next();
			final Object key = entry.getKey();
			final Object val = entry.getValue();

			db_d.put(key, val);
		}

		final String sql = "update t_department " + " set " + " name = ?, " + " manager = ?, " + " manager_name = ? " + " where id = ?";

		final Connection conn = getConn();

		final PreparedStatement stmt = conn.prepareStatement(sql);

		stmt.setString(1, ToString(db_d.get("name")));
		stmt.setString(2, ToString(db_d.get("manager")));
		stmt.setString(3, ToString(db_d.get("manager_name")));
		stmt.setString(4, ToString(db_d.get("id")));

		stmt.executeUpdate();
		stmt.close();
		conn.close();
	}

	// ///////////////////////////////////////////////////////////////
	private Connection getConn() throws Exception
	{
		Class.forName(driver).newInstance();
		Connection conn = null;
		if ((user == null) || user.equals(""))
		{
			conn = java.sql.DriverManager.getConnection(url);
		}
		else
		{
			conn = java.sql.DriverManager.getConnection(url, user, pwd);
		}

		return conn;
	}

	public ArrayList DBSelect(String sql) throws Exception
	{
		final Connection conn = getConn();
		final Statement stmt = conn.createStatement();

		final ResultSet rst = stmt.executeQuery(sql);
		final ArrayList list = ResultSetToList(rst);

		rst.close();
		stmt.close();
		conn.close();

		return list;
	}

	private static ArrayList ResultSetToList(ResultSet rs) throws Exception
	{
		final ResultSetMetaData md = rs.getMetaData();
		final int columnCount = md.getColumnCount();
		final ArrayList list = new ArrayList();
		Map rowData;
		while (rs.next())
		{
			rowData = new HashMap(columnCount);
			for (int i = 1; i <= columnCount; i++)
			{
				Object v = rs.getObject(i);

				if ((v != null) && ((v.getClass() == Date.class) || (v.getClass() == java.sql.Date.class)))
				{
					final Timestamp ts = rs.getTimestamp(i);
					v = new java.util.Date(ts.getTime());
				}
				else if ((v != null) && (v.getClass() == Clob.class))
				{
					v = clob2String((Clob) v);
				}
				rowData.put(md.getColumnName(i), v);
			}
			list.add(rowData);
		}
		return list;
	}

	private static String clob2String(Clob clob) throws Exception
	{
		return (clob != null ? clob.getSubString(1, (int) clob.length()) : null);
	}

	private int ToInt(Object o)
	{
		if (o == null)
			return 0;
		final double d = Double.parseDouble(o.toString());
		int i = 0;
		i -= d;
		return -i;
	}

	private String ToString(Object o)
	{
		if (o == null)
			return "";
		return o.toString();
	}

	private Timestamp ToDate(Object o)
	{
		return o != null ? new java.sql.Timestamp(((Date) o).getTime()) : null;
	}
}
